--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_PRO_LOAF.HQL
--    Functions : 表17：贷款发生额分产品统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_PRO_LOAF PARTITION (DATA_DATE = '20180331')
SELECT  AREA.AREA_CODE_4               AS FIN_ORG_DIST
       ,T.CCY
       ,T.BUSINESS_TYPE
       ,T.TYPE_DESC
       ,SUM(T.OCCUR_AMOUNT)/100000000     -- 贷款发生额
       ,SUM(T.AMOUNT_LM    )/100000000    -- 发生额上期末
       ,SUM(T.AMOUNT_YS    )/100000000    -- 当年累计发生额
       ,SUM(T.AMOUNT_GROW  )/100000000    -- 同比增减
       ,SUM(T.WSUM_AMOUNT  )/100000000    -- 发生额加权值
       ,SUM(T.WAMOUNT_LM   )/100000000    -- 发生额加权值上期末
       ,SUM(T.WAMOUNT_YS   )/100000000    -- 发生额加权值当年累计
       ,SUM(T.WAMOUNT_LC   )/100000000    -- 发生额加权值去年同期
FROM (
    -- 个人+机构
    SELECT
     FSE.FIN_ORG_NO                                                    AS FIN_ORG_NO
    ,FSE.CCY                                                           AS CCY
    ,FSE.CLIENT_TYPE                                                   AS BUSINESS_TYPE -- 产品类型
    ,CASE WHEN FSE.CLIENT_TYPE='1' THEN '个人'
          WHEN FSE.CLIENT_TYPE='0' THEN '机构' END                     AS TYPE_DESC     -- 产品类别描述
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                      AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                         AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                         AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)          AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                       AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                        AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                        AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                        AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='20180331') FSE

    UNION ALL

    -- 个人_普通贷款
    SELECT
     FSE.FIN_ORG_NO                                                    AS FIN_ORG_NO
    ,FSE.CCY                                                           AS CCY
    ,'11'                                                              AS BUSINESS_TYPE
    ,'普通贷款'                                                        AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                      AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                         AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                         AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)          AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                       AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                        AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                        AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                        AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219','F02201','F02202')AND DATA_DATE ='20180331') FSE

UNION ALL

    -- 个人_消费贷款
    SELECT
     FSE.FIN_ORG_NO                                                                                              AS FIN_ORG_NO
    ,FSE.CCY                                                                                                     AS CCY
    ,'111'                                                                                                       AS BUSINESS_TYPE
    ,'消费贷款'                                                                                                  AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                      AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                         AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                         AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)          AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                       AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                        AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                        AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                        AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219')AND DATA_DATE='20180331') FSE

    UNION ALL

    -- 个人_个人住房贷款+个人汽车消费贷款+其他消费贷款+经营贷款
    SELECT
     FSE.FIN_ORG_NO                                                                              AS FIN_ORG_NO
    ,FSE.CCY                                                                                     AS CCY
    ,CASE WHEN FSE.BUSINESS_TYPE='F0211' THEN '1111' -- 个人住房贷款
          WHEN FSE.BUSINESS_TYPE='F0212' THEN '1112' -- 个人汽车消费贷款
          WHEN FSE.BUSINESS_TYPE='F0219' THEN '1114' -- 其他消费贷款
          WHEN FSE.BUSINESS_TYPE IN('F02201','F02202') THEN '112'  -- 经营贷款
     END                                                                                         AS BUSINESS_TYPE
    ,CASE WHEN FSE.BUSINESS_TYPE='F0211' THEN '个人住房贷款'
          WHEN FSE.BUSINESS_TYPE='F0212' THEN '个人汽车消费贷款'
          WHEN FSE.BUSINESS_TYPE='F0219' THEN '其他消费贷款'
          WHEN FSE.BUSINESS_TYPE IN('F02201','F02202') THEN '经营贷款'
     END                                                                                         AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                                AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                                                   AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                                                   AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                                    AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                                 AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                                                  AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                                                  AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                                                  AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02201','F02202','F0219')AND DATA_DATE='20180331') FSE

    UNION ALL

    -- 个人_助学贷款
    SELECT
     FSE.FIN_ORG_NO                                                                  AS FIN_ORG_NO
    ,FSE.CCY                                                                         AS CCY
    ,'1113'                                                                          AS BUSINESS_TYPE
    ,'助学贷款'                                                                      AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                    AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                                       AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                                       AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                        AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                     AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                                      AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                                      AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                                      AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F02131','F02132')AND DATA_DATE='20180331') FSE

UNION ALL

    -- 机构_普通贷款
    SELECT
     FSE.FIN_ORG_NO                                                            AS FIN_ORG_NO
    ,FSE.CCY                                                                   AS CCY
    ,'21'                                                                      AS BUSINESS_TYPE
    ,'普通贷款'                                                                AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                              AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                                 AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                                 AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                  AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                               AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                                AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                                AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                                AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F02203','F023')AND DATA_DATE='20180331') FSE

    UNION ALL

    -- 机构_贸易融资
    SELECT
     FSE.FIN_ORG_NO                                                            AS FIN_ORG_NO
    ,FSE.CCY                                                                   AS CCY
    ,'22'                                                                      AS BUSINESS_TYPE
    ,'贸易融资'                                                                AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                              AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                                 AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                                 AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                  AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                               AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                                AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                                AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                                AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F081','F082')AND DATA_DATE IN('20180331','20180228','20170331','20180101')) FSE

    UNION ALL

    -- 机构_经营贷款+固定资产贷款+融资租赁+转贷款+并购贷款
    SELECT
     FSE.FIN_ORG_NO                                                            AS FIN_ORG_NO
    ,FSE.CCY                                                                   AS CCY
    ,CASE WHEN FSE.BUSINESS_TYPE='F02203' THEN '211'
          WHEN FSE.BUSINESS_TYPE='F023'   THEN '212'
          WHEN FSE.BUSINESS_TYPE='F09'    THEN '23'
          WHEN FSE.BUSINESS_TYPE='F11'    THEN '24'
          WHEN FSE.BUSINESS_TYPE='F12'    THEN '25'
     END                                                                       AS BUSINESS_TYPE
    ,CASE WHEN FSE.BUSINESS_TYPE='F02203' THEN '经营贷款'
          WHEN FSE.BUSINESS_TYPE='F023'   THEN '固定资产贷款'
          WHEN FSE.BUSINESS_TYPE='F09'    THEN '融资租赁'
          WHEN FSE.BUSINESS_TYPE='F11'    THEN '转贷款'
          WHEN FSE.BUSINESS_TYPE='F12'    THEN '并购贷款'
     END                                                                       AS TYPE_DESC
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                              AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                                 AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                                 AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                  AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                               AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                                AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                                AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                                AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F02203','F023','F09','F11','F12')AND DATA_DATE='20180331') FSE

    UNION ALL

    -- 合计
    SELECT
     LOAF.FIN_ORG_NO                                                           AS FIN_ORG_NO
    ,LOAF.CCY                                                                  AS CCY
    ,'3'                                                                       AS BUSINESS_TYPE
    ,'合计'                                                                    AS TYPE_DESC
    ,COALESCE(LOAF.OCCUR_AMOUNT,0)                                              AS OCCUR_AMOUNT  -- 贷款发生额
    ,COALESCE(LOAF.AMOUNT_LM,0)                                                 AS AMOUNT_LM     -- 发生额上期末
    ,COALESCE(LOAF.AMOUNT_YS,0)                                                 AS AMOUNT_YS     -- 当年累计发生额
    ,COALESCE(LOAF.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                  AS AMOUNT_GROW   -- 同比增减
    ,COALESCE(LOAF.WSUM_AMOUNT,0)                                               AS WSUM_AMOUNT   -- 发生额加权值
    ,COALESCE(LOAF.WAMOUNT_LM,0)                                                AS WAMOUNT_LM    -- 发生额加权值上期末
    ,COALESCE(LOAF.WAMOUNT_YS,0)                                                AS WAMOUNT_YS    -- 发生额加权值当年累计
    ,COALESCE(LOAF.WAMOUNT_YC,0)                                                AS WAMOUNT_LC    -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='20180331') LOAF
   )T
  -- 与地区表最细级关联
  LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
  LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
   GROUP BY  AREA.AREA_CODE_4
            ,T.CCY
            ,T.BUSINESS_TYPE
            ,T.TYPE_DESC
;